package com.han.dao.concrete.product;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import javax.naming.NamingException;

import com.han.bom.Answer;
import com.han.bom.Exam;
import com.han.bom.ExamResponse;
import com.han.bom.MultipleChoice;
import com.han.bom.PracticeExam;
import com.han.bom.Question;
import com.han.bom.RealExam;
import com.han.bom.Student;
import com.han.bom.TrueOrFalse;
import com.han.dao.abstraction.product.GenericExamResponseDAO;

public class ExamResponseDAO extends DataAccessObject implements
		GenericExamResponseDAO {

	/**
	 * 
	 */
	private static final long serialVersionUID = 5602547041131690715L;

	public ArrayList<String> retrieveAllResponses(String profId)
			throws SQLException, ClassNotFoundException, NamingException {
		ArrayList<String> examstaken = new ArrayList<String>();
		String examtaken = "";
		int id = 0;

		try {
			id = Integer.parseInt(profId.substring(1));
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			this.connect();
			String select = "SELECT a.exam_response_id FROM tbl_exam_response AS a INNER JOIN tbl_professor_student AS b ON a.student_id=b.student_id WHERE b.professor_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.resultSet = this.preparedStatement.executeQuery();

			while (this.resultSet.next()) {
				examtaken = this.resultSet.getString(1);
				examstaken.add(examtaken);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return examstaken;
	}

	public ExamResponse retrieveResponseById(String erId) throws SQLException,
			ClassNotFoundException, NamingException {
		ExamResponse response = null;
		Exam exam = null;
		Date date = null;
		int index1 = erId.indexOf("S");
		int index2 = erId.indexOf("T");
		String studentId = erId.substring(index1, index2);
		Question question = null;
		String answerid = "";
		int index = 0;
		double grade = 0;
		boolean type = false;

		try {
			this.connect();

			String select = "SELECT d.exam_id,d.exam_name,d.exam_duration,d.exam_passing,d.exam_type,c.date_taken,c.exam_grade,a.answer,a.answer_id FROM tbl_choice AS a INNER JOIN tbl_questions_answered AS b ON a.answer_id = b.answer_id RIGHT OUTER JOIN tbl_exam_response AS c ON b.exam_response_id=c.exam_response_id INNER JOIN tbl_exam AS d ON c.exam_id=d.exam_id WHERE c.exam_response_id=?";

			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setString(1, erId);
			this.resultSet = this.preparedStatement.executeQuery();

			response = new ExamResponse(new RealExam(), studentId,
					new ArrayList<Answer>());
			response.setExamResponseID(erId);
			while (resultSet.next()) {
				type = this.resultSet.getBoolean("exam_type");

				if (type) {
					exam = new RealExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "RE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDuration(this.resultSet.getLong("exam_duration"));
					exam.setPassingPercent(this.resultSet
							.getDouble("exam_passing"));
				} else {
					exam = new PracticeExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "PE");
					exam.setExamName(this.resultSet.getString("exam_name"));
				}

				date = new Date(this.resultSet.getTimestamp("date_taken")
						.getTime());
				grade = this.resultSet.getDouble("exam_grade");

				if (this.resultSet.getString("answer") != null) {
					answerid = this.resultSet.getString("answer_id");
					index = answerid.indexOf("F");

					if (index != -1) {
						question = new TrueOrFalse();
						question.setQuestionID(answerid.substring(0, index + 1));
					} else {
						index = answerid.indexOf("C");
						question = new MultipleChoice();
						question.setQuestionID(answerid.substring(0, index + 1));
					}

					String answer = this.resultSet.getString("answer");
					Answer ans = new Answer(question, answer);
					response.getAnswers().add(ans);
				}
			}
			response.setExam(exam);
			response.setDateTaken(date);
			response.setGrade(grade);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return response;
	}

	public ArrayList<ExamResponse> retrieveResponsesByStudentAndExam(
			String studentId, Exam exam) throws SQLException,
			ClassNotFoundException, NamingException {
		ArrayList<ExamResponse> responses = new ArrayList<ExamResponse>();
		ExamResponse response = null;
		Question question = null;
		int count = (exam instanceof RealExam) ? ((RealExam) exam)
				.getTimesActivated() : ((PracticeExam) exam).getTimesTaken();
		String examrespid = "";

		try {
			this.connect();

			for (int i = 1; i <= count; i++) {
				examrespid = "T" + i;
				String select = "SELECT * FROM tbl_choice AS a INNER JOIN tbl_questions_answered AS b ON a.answer_id = b.answer_id RIGHT OUTER JOIN tbl_exam_response AS c ON b.exam_response_id=c.exam_response_id WHERE c.exam_response_id=?";
				this.preparedStatement = this.connection
						.prepareStatement(select);
				this.preparedStatement.setString(1, exam.getExamID()
						+ studentId + examrespid);
				this.resultSet = this.preparedStatement.executeQuery();

				if (resultSet.isBeforeFirst()) {
					response = new ExamResponse(exam, studentId,
							new ArrayList<Answer>());
					response.setExamResponseID(exam.getExamID() + studentId
							+ examrespid);
					while (resultSet.next()) {
						if (this.resultSet.getString("answer") != null) {
							int questionid = this.resultSet
									.getInt("question_id");

							question = exam.searchQuestion("Q" + questionid
									+ "MC");

							if (question == null) {
								question = exam.searchQuestion("Q" + questionid
										+ "TF");
							}

							String answer = this.resultSet.getString("answer");
							Answer ans = new Answer(question, answer);
							response.getAnswers().add(ans);
						}
						response.setDateTaken(new Date(this.resultSet
								.getTimestamp("date_taken").getTime()));
						response.setGrade(this.resultSet
								.getDouble("exam_grade"));
					}
					responses.add(response);
				}
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}
		return responses;
	}

	public ArrayList<String> retrieveExamsTaken(String studentId)
			throws SQLException, ClassNotFoundException, NamingException {
		ArrayList<String> examstaken = new ArrayList<String>();
		String examtaken = "";
		int id = 0;
		boolean type = false;

		try {
			id = Integer.parseInt(studentId.substring(1));
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			this.connect();
			String select = "SELECT DISTINCT e.exam_id, e.exam_type FROM tbl_exam AS e INNER JOIN tbl_exam_response AS b ON e.exam_id = b.exam_id WHERE b.student_id = ? AND b.istaken = true";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.resultSet = this.preparedStatement.executeQuery();

			while (this.resultSet.next()) {
				type = this.resultSet.getBoolean(2);

				if (type) {
					examtaken = "E" + this.resultSet.getInt(1) + "RE";
				} else {
					examtaken = "E" + this.resultSet.getInt(1) + "PE";
				}
				examstaken.add(examtaken);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return examstaken;
	}

	public ArrayList<ExamResponse> retrieveExamsMissed(String studentId)
			throws SQLException, ClassNotFoundException, NamingException {
		ArrayList<ExamResponse> examstaken = new ArrayList<ExamResponse>();
		RealExam examtaken = null;
		ExamResponse missed = null;
		int id = 0;
		Date date = null;

		try {
			id = Integer.parseInt(studentId.substring(1));
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			this.connect();
			String select = "SELECT a.exam_id,a.exam_name,a.exam_duration,a.exam_passing,b.date_taken FROM tbl_exam AS a INNER JOIN tbl_exam_response AS b ON a.exam_id=b.exam_id WHERE b.student_id=? AND b.istaken=false";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.resultSet = this.preparedStatement.executeQuery();

			while (this.resultSet.next()) {
				examtaken = new RealExam();
				examtaken.setExamID("E" + this.resultSet.getInt(1) + "RE");
				examtaken.setExamName(this.resultSet.getString(2));
				examtaken.setDuration(this.resultSet.getLong(3));
				examtaken.setPassingPercent(this.resultSet.getDouble(4));
				date = new Date(this.resultSet.getTimestamp(5).getTime());
				missed = new ExamResponse(examtaken, studentId,
						new ArrayList<Answer>());
				missed.setDateTaken(date);
				examstaken.add(missed);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return examstaken;
	}

	public int retrieveResponseCountByStudentAndExam(String studentId,
			String examId) throws SQLException, ClassNotFoundException,
			NamingException {
		int studentid = Integer.parseInt(studentId.substring(1));
		int examid = 0;
		int count = 0;

		try {
			examid = Integer.parseInt(examId.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			examid = 0;
		}

		try {
			this.connect();
			String select = "SELECT COUNT(*) FROM tbl_exam_response WHERE student_id=? AND exam_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, studentid);
			this.preparedStatement.setInt(2, examid);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				count = this.resultSet.getInt(1);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}
		return count;
	}

	public boolean insertExamResponse(Student student, boolean taken)
			throws ClassNotFoundException, SQLException, NamingException {
		boolean success = false;
		String insert = "INSERT IGNORE INTO tbl_exam_response(exam_response_id,student_id,exam_id,date_taken,istaken,submitted,exam_grade) VALUES(?,?,?,?,?,?,?)";
		Date now = new Date();
		int eid = 0;

		try {
			eid = Integer.parseInt(student.getExamAnswers().get(0).getExam()
					.getExamID().replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			eid = 0;
		}

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);

			int id = Integer.parseInt(student.getUserID().substring(1));
			this.preparedStatement.setString(1, student.getExamAnswers().get(0)
					.getExamResponseID());
			this.preparedStatement.setInt(2, id);
			this.preparedStatement.setInt(3, eid);
			this.preparedStatement
					.setTimestamp(4, new Timestamp(now.getTime()));
			this.preparedStatement.setBoolean(5, taken);
			this.preparedStatement.setBoolean(6, false);
			this.preparedStatement.setDouble(7, 0);
			int row = this.preparedStatement.executeUpdate();
			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}

	public boolean updateExamResponseGrade(ExamResponse response)
			throws ClassNotFoundException, SQLException, NamingException {
		boolean success = false;
		String update = "UPDATE tbl_exam_response SET exam_grade=? WHERE exam_response_id=?";
		int row = 0;

		try {
			this.connect();
			this.preparedStatement = this.connection.prepareStatement(update);

			this.preparedStatement.setDouble(1, response.getGrade());
			this.preparedStatement.setString(2, response.getExamResponseID());
			row = this.preparedStatement.executeUpdate();

			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return success;
	}

	public boolean updateExamResponseStatus(ExamResponse response)
			throws ClassNotFoundException, SQLException, NamingException {
		boolean success = false;
		String update = "UPDATE tbl_exam_response SET submitted=? WHERE exam_response_id=?";
		int row = 0;

		try {
			this.connect();
			this.preparedStatement = this.connection.prepareStatement(update);

			this.preparedStatement.setBoolean(1, response.isSubmitted());
			this.preparedStatement.setString(2, response.getExamResponseID());
			row = this.preparedStatement.executeUpdate();

			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return success;
	}

	public boolean updateExamResponses(ExamResponse response)
			throws ClassNotFoundException, SQLException, NamingException {
		boolean success = false;
		String update = "UPDATE tbl_questions_answered SET answer_id=(SELECT answer_id FROM tbl_choice WHERE question_id=? AND answer=?) WHERE exam_response_id=? AND question_id=?";
		int row = 0;

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(update);

			for (Answer answer : response.getAnswers()) {
				int questionid = Integer.parseInt(answer.getQuestion()
						.getQuestionID().replaceAll("[^\\d]", ""));

				this.preparedStatement.setInt(1, questionid);
				this.preparedStatement.setString(2, answer.getAnswer());
				this.preparedStatement.setString(3,
						response.getExamResponseID());
				this.preparedStatement.setInt(4, questionid);
				row += this.preparedStatement.executeUpdate();
			}

			this.connection.commit();

			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}

	public boolean deleteExamResponses(String examresponseid)
			throws ClassNotFoundException, SQLException, NamingException {
		boolean success = false;
		String delete = "DELETE FROM tbl_questions_answered WHERE exam_response_id=?";

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(delete);

			this.preparedStatement.setString(1, examresponseid);
			int row = this.preparedStatement.executeUpdate();

			delete = "DELETE FROM tbl_exam_response WHERE exam_response_id=?";

			this.preparedStatement = this.connection.prepareStatement(delete);

			this.preparedStatement.setString(1, examresponseid);
			int row2 = this.preparedStatement.executeUpdate();
			this.connection.commit();

			success = (row > 0 && row2 > 0) ? true : false;
		} catch (SQLException e) {
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}

	public boolean submitResponses(ExamResponse response) throws SQLException,
			NamingException {
		boolean success = false;
		String insert = "INSERT INTO tbl_questions_answered(exam_response_id,question_id,answer_id) VALUES(?,?,(SELECT answer_id FROM tbl_choice WHERE question_id = ? AND answer=?))";
		int row2 = 0;
		int size = 0;

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);

			for (Answer answer : response.getAnswers()) {
				if (!answer.getAnswer().equals("")) {
					int questionid = Integer.parseInt(answer.getQuestion()
							.getQuestionID().replaceAll("[^\\d]", ""));
					this.preparedStatement.setString(1,
							response.getExamResponseID());
					this.preparedStatement.setInt(2, questionid);
					this.preparedStatement.setInt(3, questionid);
					this.preparedStatement.setString(4, answer.getAnswer());
					size++;
					row2 += this.preparedStatement.executeUpdate();
				}
			}
			this.connection.commit();
			success = (row2 == size) ? true : false;
		} catch (SQLException e) {
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}

	@Override
	public ArrayList<ExamResponse> retrieveMissedRealExamsByKeyword(String key,
			String studentId) throws SQLException, ClassNotFoundException,
			NamingException {
		// TODO Auto-generated method stub
		ArrayList<ExamResponse> examstaken = new ArrayList<ExamResponse>();
		RealExam examtaken = null;
		ExamResponse missed = null;
		int id = 0;
		int studentid = 0;
		Date date = null;
		String intkey = "%" + key + "%";

		try {
			if (key.contains("E") && (key.contains("PE") || key.contains("RE"))) {
				id = Integer.parseInt(key.replaceAll("[^\\d]", ""));
			}
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			studentid = Integer.parseInt(studentId.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			studentid = 0;
		}

		try {
			this.connect();
			String select = "SELECT a.exam_id,a.exam_name,a.exam_duration,a.exam_passing,b.date_taken FROM tbl_exam AS a INNER JOIN tbl_exam_response AS b ON a.exam_id=b.exam_id WHERE b.student_id=? AND b.istaken=false AND (a.exam_id = ? OR UPPER(a.exam_name) LIKE UPPER(?))";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, studentid);
			this.preparedStatement.setInt(2, id);
			this.preparedStatement.setString(3, intkey);
			this.resultSet = this.preparedStatement.executeQuery();

			while (this.resultSet.next()) {
				examtaken = new RealExam();
				examtaken.setExamID("E" + this.resultSet.getInt(1) + "RE");
				examtaken.setExamName(this.resultSet.getString(2));
				examtaken.setDuration(this.resultSet.getLong(3));
				examtaken.setPassingPercent(this.resultSet.getDouble(4));
				date = new Date(this.resultSet.getTimestamp(5).getTime());
				missed = new ExamResponse(examtaken, studentId,
						new ArrayList<Answer>());
				missed.setDateTaken(date);
				examstaken.add(missed);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return examstaken;
	}

	@Override
	public ArrayList<ExamResponse> retrieveRealExamsTakenByKeyword(String key,
			String studentId) throws SQLException, ClassNotFoundException,
			NamingException {
		// TODO Auto-generated method stub
		ArrayList<ExamResponse> examstaken = new ArrayList<ExamResponse>();
		RealExam examtaken = null;
		ExamResponse missed = null;
		int id = 0;
		int studentid = 0;
		Date date = null;
		String intkey = "%" + key + "%";

		try {
			if (key.contains("E") && (key.contains("PE") || key.contains("RE"))) {
				id = Integer.parseInt(key.replaceAll("[^\\d]", ""));
			}
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			studentid = Integer.parseInt(studentId.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			studentid = 0;
		}

		try {
			this.connect();
			String select = "SELECT a.exam_id,a.exam_name,a.exam_duration,a.exam_passing,b.date_taken FROM tbl_exam AS a INNER JOIN tbl_exam_response AS b ON a.exam_id=b.exam_id WHERE b.student_id=? AND b.istaken=true AND a.exam_type = true AND (a.exam_id = ? OR UPPER(a.exam_name) LIKE UPPER(?))";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, studentid);
			this.preparedStatement.setInt(2, id);
			this.preparedStatement.setString(3, intkey);
			this.resultSet = this.preparedStatement.executeQuery();

			while (this.resultSet.next()) {
				examtaken = new RealExam();
				examtaken.setExamID("E" + this.resultSet.getInt(1) + "RE");
				examtaken.setExamName(this.resultSet.getString(2));
				examtaken.setDuration(this.resultSet.getLong(3));
				examtaken.setPassingPercent(this.resultSet.getDouble(4));
				date = new Date(this.resultSet.getTimestamp(5).getTime());
				missed = new ExamResponse(examtaken, studentId,
						new ArrayList<Answer>());
				missed.setDateTaken(date);
				examstaken.add(missed);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return examstaken;
	}

	@Override
	public ArrayList<ExamResponse> retrievePracticeExamsTakenByKeyword(
			String key, String studentId) throws SQLException,
			ClassNotFoundException, NamingException {
		// TODO Auto-generated method stub
		ArrayList<ExamResponse> examstaken = new ArrayList<ExamResponse>();
		RealExam examtaken = null;
		ExamResponse missed = null;
		int id = 0;
		int studentid = 0;
		Date date = null;
		String intkey = "%" + key + "%";

		try {
			if (key.contains("E") && (key.contains("PE") || key.contains("RE"))) {
				id = Integer.parseInt(key.replaceAll("[^\\d]", ""));
			}
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			studentid = Integer.parseInt(studentId.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			studentid = 0;
		}

		try {
			this.connect();
			String select = "SELECT a.exam_id,a.exam_name,a.exam_duration,a.exam_passing,b.date_taken FROM tbl_exam AS a INNER JOIN tbl_exam_response AS b ON a.exam_id=b.exam_id WHERE b.student_id=? AND b.istaken=true AND a.exam_type=false AND (a.exam_id = ? OR UPPER(a.exam_name) LIKE UPPER(?))";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, studentid);
			this.preparedStatement.setInt(2, id);
			this.preparedStatement.setString(3, intkey);
			this.resultSet = this.preparedStatement.executeQuery();

			while (this.resultSet.next()) {
				examtaken = new RealExam();
				examtaken.setExamID("E" + this.resultSet.getInt(1) + "RE");
				examtaken.setExamName(this.resultSet.getString(2));
				examtaken.setDuration(this.resultSet.getLong(3));
				examtaken.setPassingPercent(this.resultSet.getDouble(4));
				date = new Date(this.resultSet.getTimestamp(5).getTime());
				missed = new ExamResponse(examtaken, studentId,
						new ArrayList<Answer>());
				missed.setDateTaken(date);
				examstaken.add(missed);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return examstaken;
	}
}
